const mysql = require('mysql2/promise');
require('dotenv').config({ path: '.env.local' });

async function recreateInternshipsTable() {
  try {
    console.log('正在连接到MySQL...');
    
    // 创建数据库连接
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '123456',
      database: process.env.DB_NAME || 'xiaoyoubao',
      port: Number(process.env.DB_PORT) || 3306
    });

    console.log('已连接到MySQL服务器');
    
    // 首先备份现有数据
    console.log('正在备份现有实习经历数据...');
    const [internships] = await connection.query('SELECT * FROM internships');
    console.log(`已备份 ${internships.length} 条实习经历记录`);
    
    // 删除旧表
    console.log('正在删除旧的实习经历表...');
    await connection.query('DROP TABLE IF EXISTS internships');
    
    // 创建新的实习经历表
    console.log('正在创建新的实习经历表...');
    await connection.query(`
      CREATE TABLE internships (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        company VARCHAR(100) NOT NULL,
        position VARCHAR(100) NOT NULL,
        period VARCHAR(100) NOT NULL,
        description TEXT,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    `);
    
    // 恢复备份的数据
    if (internships.length > 0) {
      console.log('正在恢复实习经历数据...');
      
      for (const internship of internships) {
        // 如果旧数据中有start_date和end_date，则转换为period格式
        let period = internship.period;
        if (!period && (internship.start_date || internship.end_date)) {
          const startDate = internship.start_date 
            ? new Date(internship.start_date).toISOString().split('T')[0].substring(0, 7) 
            : '未知';
          const endDate = internship.end_date 
            ? new Date(internship.end_date).toISOString().split('T')[0].substring(0, 7) 
            : '至今';
          period = `${startDate} 至 ${endDate}`;
        }
        
        if (!period) {
          period = '未知时间段';
        }
        
        await connection.query(`
          INSERT INTO internships (
            id, user_id, company, position, period, description, 
            created_at, updated_at
          ) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())
        `, [
          internship.id,
          internship.user_id,
          internship.company,
          internship.position,
          period,
          internship.description || null
        ]);
      }
      
      console.log(`成功恢复 ${internships.length} 条实习经历记录`);
    }
    
    console.log('实习经历表重建成功！');
    await connection.end();
    
  } catch (error) {
    console.error('重建实习经历表时出错:', error);
    process.exit(1);
  }
}

recreateInternshipsTable(); 